package dao;

import utils.connect.ConnectSqlDaoImpl;
import utils.string.sql.T_Using;
import vo.Using;

import java.sql.*;
import java.util.HashMap;

public class UsingDaoImpl implements UsingDao {
    ConnectSqlDaoImpl connectSqlDao = ConnectSqlDaoImpl.getInStance();

    @Override
    public Using selectById(int id) {
        Using using = new Using();
        try {
            connectSqlDao.initConnect();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Connection connection = connectSqlDao.getConnection();
        String sql = "select * from "+ T_Using.TABLE_NAME+" where "
                +T_Using.USING_ID+" = ?";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                using.setEquip_id(resultSet.getInt(T_Using.EQUIP_ID));
                using.setUsing_date(resultSet.getDate(T_Using.USING_DATE));
                using.setUser_id(resultSet.getInt(T_Using.USER_ID));
                using.setUsing_reason(resultSet.getString(T_Using.USING_REASON));
                using.setUsing_num(resultSet.getInt(T_Using.USING_NUM));
                using.setUsing_id(resultSet.getInt(T_Using.USING_ID));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connectSqlDao.closeConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return using;
    }

    @Override
    public boolean deleteByID(int id) {
        try {
            connectSqlDao.initConnect();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Connection connection = connectSqlDao.getConnection();
        String sql = "delete from "+ T_Using.TABLE_NAME+" where "
        +T_Using.USING_ID+" = ?";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            preparedStatement.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connectSqlDao.closeConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    @Override
    public HashMap selectAll() {
        HashMap usings= new HashMap();
        try {
            connectSqlDao.initConnect();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Connection connection = connectSqlDao.getConnection();
        String sql = "select * from "+ T_Using.TABLE_NAME;
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                Using using = new Using();
                using.setEquip_id(resultSet.getInt(T_Using.EQUIP_ID));
                using.setUsing_date(resultSet.getDate(T_Using.USING_DATE));
                using.setUser_id(resultSet.getInt(T_Using.USER_ID));
                using.setUsing_reason(resultSet.getString(T_Using.USING_REASON));
                using.setUsing_num(resultSet.getInt(T_Using.USING_NUM));
                using.setUsing_id(resultSet.getInt(T_Using.USING_ID));
                usings.put(using.getUsing_id(),using);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connectSqlDao.closeConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return usings;
    }

    @Override
    public boolean updateById(Using using, int id) {
        try {
            connectSqlDao.initConnect();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Connection connection = connectSqlDao.getConnection();
        String sql = "update "+ T_Using.TABLE_NAME+" set "
                +T_Using.USER_ID+"= ? "
                +T_Using.EQUIP_ID + " = ?"
                +T_Using.USING_DATE + " = ?"
                +T_Using.USING_REASON + " = ?"
                +T_Using.USING_NUM + " = ?"
                + " where "+ T_Using.USING_ID+" = ?";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,using.getUser_id());
            preparedStatement.setInt(2,using.getEquip_id());
            preparedStatement.setDate(3, (Date) using.getUsing_date());
            preparedStatement.setString(4,using.getUsing_reason());
            preparedStatement.setInt(5,using.getUsing_num());
            preparedStatement.setInt(6,using.getUsing_id());
            preparedStatement.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connectSqlDao.closeConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    @Override
    public boolean insertUsing(Using using) {
        try {
            connectSqlDao.initConnect();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Connection connection = connectSqlDao.getConnection();
        String sql = "insert into "+ T_Using.TABLE_NAME+"("
                +T_Using.USER_ID+","
                +T_Using.EQUIP_ID + ","
                +T_Using.USING_DATE + " ,"
                +T_Using.USING_REASON + ","
                +T_Using.USING_NUM + " )"
                + " values (?,?,?,?,?)";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,using.getUser_id());
            preparedStatement.setInt(2,using.getEquip_id());
            preparedStatement.setDate(3, (Date) using.getUsing_date());
            preparedStatement.setString(4,using.getUsing_reason());
            preparedStatement.setInt(5,using.getUsing_num());
            preparedStatement.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connectSqlDao.closeConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    @Override
    public boolean substractUsingNum(int id, int num) {
            Using using = selectById(id);
            int originNum = using.getUsing_num();
            using.setUsing_num(originNum-num);
            return updateById(using,id);
    }
}
